﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itau.SC.Conexao;
using System.Data.SqlClient;
using System.Data;
using Itau.SC.EnterpriseLib.Data;
using System.Data.Common;

namespace itau.imnk.dados
{
    public class DataAccessSQL : IDisposable
    {
        #region Propriedades

        public IList<ParameterIn> ParameterIn
        {
            get;
            set;
        }

        public IList<ParameterOut> ParameterOut
        {
            get;
            set;
        }

        #endregion

        #region Construtor

        public DataAccessSQL()
        {
            this.ParameterIn = new List<ParameterIn>();
            this.ParameterOut = new List<ParameterOut>();
        }

        #endregion

        #region Destrutor

        ~DataAccessSQL()
        {
            Limpar();
        }

        #endregion

        #region Atributos

        Database _db;
        DbCommand _dbCommand;
        DbTransaction _transaction;
        DbConnection _connection;

        #endregion

        #region Métodos

        public void BeginTransaction()
        {
            CreateDataBase();

            _connection = _db.CreateConnection();
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
        }

        public void RollBackTransaction()
        {
            _transaction.Rollback();
        }

        public static DataAccessSQL GetDataAccess()
        {
            return new DataAccessSQL();
        }

        public void CreateDataBase()
        {
            string chaveStringConnection = System.Configuration.ConfigurationSettings.AppSettings["SQL_CONNECTION"];

            _db = DatabaseFactory.CreateDatabase(chaveStringConnection);
        }

        public DataSet ExecuteDataSet(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            DataSet resultado;
            _dbCommand = _db.GetStoredProcCommand(storedProcedureName);

            foreach (ParameterIn parameterIn in ParameterIn)
            {
                _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
            }

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
            }

            if (_transaction != null)
                resultado = _db.ExecuteDataSet(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteDataSet(_dbCommand);

            return resultado;
        }

        public DataSet ExecuteDataSetSqlCommand(string sqlCommand)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            DataSet resultado;
            _dbCommand = _db.GetSqlStringCommand(sqlCommand);

            if (_transaction != null)
                resultado = _db.ExecuteDataSet(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteDataSet(_dbCommand);

            return resultado;
        }

        public int ExecuteNonQuery(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            int resultado = 0;
            _dbCommand = _db.GetStoredProcCommand(storedProcedureName);

            foreach (ParameterIn parameterIn in ParameterIn)
            {
                _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
            }

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
            }

            if (_transaction != null)
                resultado = _db.ExecuteNonQuery(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteNonQuery(_dbCommand);

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                parameterOut.ParameterValue = _dbCommand.Parameters[parameterOut.ParameterName].Value;
            }

            return resultado;
        }

        public int ExecuteNonQuerySqlCommand(string sqlCommand)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            int resultado = 0;
            _dbCommand = _db.GetStoredProcCommand(sqlCommand);

            if (_transaction != null)
                resultado = _db.ExecuteNonQuery(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteNonQuery(_dbCommand);

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                parameterOut.ParameterValue = _dbCommand.Parameters[parameterOut.ParameterName].Value;
            }

            return resultado;
        }

        public object ExecuteScalar(string storedProcedureName)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            object resultado = null;
            _dbCommand = _db.GetStoredProcCommand(storedProcedureName);

            foreach (ParameterIn parameterIn in ParameterIn)
            {
                _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
            }

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
            }

            if (_transaction != null)
                resultado = _db.ExecuteScalar(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteScalar(_dbCommand);

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                parameterOut.ParameterValue = _dbCommand.Parameters[parameterOut.ParameterName].Value;
            }

            return resultado;
        }

        public object ExecuteScalarSqlCommand(string sqlCommand)
        {
            if (_db == null)
            {
                CreateDataBase();
                _connection = _db.CreateConnection();
                _connection.Open();
            }

            object resultado = null;
            _dbCommand = _db.GetStoredProcCommand(sqlCommand);

            foreach (ParameterIn parameterIn in ParameterIn)
            {
                _db.AddInParameter(_dbCommand, parameterIn.ParameterName, parameterIn.ParameterType, parameterIn.ParameterValue);
            }

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                _db.AddOutParameter(_dbCommand, parameterOut.ParameterName, parameterOut.ParameterType, parameterOut.ParameterSize);
            }

            if (_transaction != null)
                resultado = _db.ExecuteScalar(_dbCommand, _transaction);
            else
                resultado = _db.ExecuteScalar(_dbCommand);

            foreach (ParameterOut parameterOut in ParameterOut)
            {
                parameterOut.ParameterValue = _dbCommand.Parameters[parameterOut.ParameterName].Value;
            }

            return resultado;
        }

        public void Dispose()
        {
            Limpar();
            GC.SuppressFinalize(this);
        }

        private void Limpar()
        {
            if (_connection != null && _connection.State != ConnectionState.Closed)
            {
                _connection.Close();
            }

            if (_db != null)
            {
                _db = null;
            }

            if (this.ParameterIn != null)
            {
                this.ParameterIn.Clear();
            }

            if (this.ParameterOut != null)
            {
                this.ParameterOut.Clear();
            }

            if (_dbCommand != null)
            {
                _dbCommand.Dispose();
                _dbCommand = null;
            }

            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }

            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
        }

        #endregion
    }
}
